from typing import Optional, List, Dict, Any
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import and_, or_, func, desc
from models.petition_record import (
    PetitionRecord,
    RegionalAnalysisResult,
    ComplaintTypeAnalysisResult,
    SentimentAnalysisResult,
    ComplianceAnalysisResult,
    DuplicateAnalysisResult
)
import json
import pandas as pd
from datetime import datetime
import os
import logging

# 配置日志
logger = logging.getLogger(__name__)

class PetitionLedgerService:
    def __init__(self, db: Session):
        self.db = db

    def get_petition_ledger_list(
        self,
        page: int = 1,
        page_size: int = 20,
        region: Optional[str] = None,
        petitioner_name: Optional[str] = None,
        respondent_unit: Optional[str] = None,
        analysis_status: Optional[str] = None,
        date_from: Optional[str] = None,
        date_to: Optional[str] = None,
        sentiment_type: Optional[str] = None,
        complaint_type: Optional[str] = None,
        task_id: Optional[int] = None,
        has_duplicates: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        获取信访台账列表
        基于区域分析结果表，关联查询其他分析结果表
        """
        # 构建查询 - 使用区域分析结果表作为主表，优化查询性能
        query = self.db.query(
            RegionalAnalysisResult,
            ComplaintTypeAnalysisResult,
            SentimentAnalysisResult,
            ComplianceAnalysisResult,
            DuplicateAnalysisResult
        ).outerjoin(
            ComplaintTypeAnalysisResult,
            and_(
                RegionalAnalysisResult.task_id == ComplaintTypeAnalysisResult.task_id,
                RegionalAnalysisResult.petition_record_id == ComplaintTypeAnalysisResult.petition_record_id
            )
        ).outerjoin(
            SentimentAnalysisResult,
            and_(
                RegionalAnalysisResult.task_id == SentimentAnalysisResult.task_id,
                RegionalAnalysisResult.petition_record_id == SentimentAnalysisResult.petition_record_id
            )
        ).outerjoin(
            ComplianceAnalysisResult,
            and_(
                RegionalAnalysisResult.task_id == ComplianceAnalysisResult.task_id,
                RegionalAnalysisResult.petition_record_id == ComplianceAnalysisResult.petition_record_id
            )
        ).outerjoin(
            DuplicateAnalysisResult,
            RegionalAnalysisResult.task_id == DuplicateAnalysisResult.task_id
            # 注意：DuplicateAnalysisResult没有petition_record_id字段，所以只通过task_id关联
        ).order_by(desc(RegionalAnalysisResult.created_at))  # 按创建时间降序排列

        # 分析任务过滤 - 添加任务ID筛选
        if task_id:
            query = query.filter(RegionalAnalysisResult.task_id == task_id)

        # 应用搜索条件
        if region:
            query = query.filter(
                or_(
                    RegionalAnalysisResult.region.like(f"%{region}%"),
                    RegionalAnalysisResult.province.like(f"%{region}%"),
                    RegionalAnalysisResult.city.like(f"%{region}%"),
                    RegionalAnalysisResult.district.like(f"%{region}%")
                )
            )

        if petitioner_name:
            query = query.filter(RegionalAnalysisResult.petitioner_name.like(f"%{petitioner_name}%"))

        if respondent_unit:
            query = query.filter(RegionalAnalysisResult.respondent_unit.like(f"%{respondent_unit}%"))

        if analysis_status:
            # 分析状态过滤，由于模型中没有对应字段，这里先注释掉
            # query = query.filter(RegionalAnalysisResult.analysis_status == analysis_status)
            pass

        if date_from:
            query = query.filter(RegionalAnalysisResult.created_at >= date_from)

        if date_to:
            query = query.filter(RegionalAnalysisResult.created_at <= date_to)

        if sentiment_type:
            query = query.filter(SentimentAnalysisResult.sentiment_type == sentiment_type)

        if complaint_type:
            query = query.filter(
                or_(
                    ComplaintTypeAnalysisResult.primary_type == complaint_type,
                    ComplaintTypeAnalysisResult.secondary_type == complaint_type,
                    ComplaintTypeAnalysisResult.tertiary_type == complaint_type
                )
            )

        # 处理重复信访筛选
        if has_duplicates is not None:
            if has_duplicates.lower() == 'true':
                # 有重复：查询存在重复分析结果的记录
                query = query.filter(DuplicateAnalysisResult.cfbh.isnot(None))
            elif has_duplicates.lower() == 'false':
                # 无重复：查询没有重复分析结果的记录
                query = query.filter(DuplicateAnalysisResult.cfbh.is_(None))

        # 获取总数
        total = query.count()

        # 分页查询
        items = query.offset((page - 1) * page_size).limit(page_size).all()

        # 转换数据格式
        result = []
        for item in items:
            regional_data = item[0]
            complaint_data = item[1]
            sentiment_data = item[2]
            compliance_data = item[3]
            duplicate_data = item[4]

            result.append({
                'id': regional_data.id,
                'petition_record_id': regional_data.petition_record_id,
                'analysis_task_id': regional_data.task_id,
                'petitioner_name': regional_data.tsr,
                'petition_content': regional_data.xfnr,  # 信访内容字段
                'respondent_unit': regional_data.ts_qxdw,
                'region': regional_data.ts_xzq,
                'province': regional_data.dzxx_ds,  # 地址信息_行政区
                'city': regional_data.dzxx_xzq,    # 地址信息_行政区
                'district': regional_data.dzxx_jd,   # 地址信息_街道
                'analysis_status': 'completed',  # 默认状态，因为没有对应字段
                'created_at': regional_data.created_at.isoformat(),
                'updated_at': regional_data.updated_at.isoformat(),
                'sentiment_type': sentiment_data.qgfx_lx if sentiment_data else None,
                'complaint_type': complaint_data.tslx_yj if complaint_data else None,
                'compliance_status': '合规' if compliance_data and compliance_data.gffx_zt == '1' else '不合规' if compliance_data else None,
                'is_duplicate': bool(duplicate_data.cfbh) if duplicate_data else False,
                'confidence': regional_data.cfxs_gl,
                'has_location': bool(regional_data.dzxx_lng and regional_data.dzxx_lat)
            })

        return {
            'items': result,
            'total': total,
            'page': page,
            'page_size': page_size,
            'total_pages': (total + page_size - 1) // page_size
        }

    def get_petition_ledger_detail(self, record_id: int) -> Dict[str, Any]:
        """
        获取信访台账详情
        """
        try:
            # 查询基础信息 - 使用区域分析结果表
            regional_data = self.db.query(RegionalAnalysisResult).filter(
                RegionalAnalysisResult.id == record_id
            ).first()

            if not regional_data:
                raise ValueError(f"未找到ID为{record_id}的记录")

            # 查询关联的分析结果 - 使用单个查询优化性能
            complaint_data = self.db.query(ComplaintTypeAnalysisResult).filter(
                ComplaintTypeAnalysisResult.task_id == regional_data.task_id,
                ComplaintTypeAnalysisResult.petition_record_id == regional_data.petition_record_id
            ).first()

            sentiment_data = self.db.query(SentimentAnalysisResult).filter(
                SentimentAnalysisResult.task_id == regional_data.task_id,
                SentimentAnalysisResult.petition_record_id == regional_data.petition_record_id
            ).first()

            compliance_data = self.db.query(ComplianceAnalysisResult).filter(
                ComplianceAnalysisResult.task_id == regional_data.task_id,
                ComplianceAnalysisResult.petition_record_id == regional_data.petition_record_id
            ).first()

            duplicate_data = self.db.query(DuplicateAnalysisResult).filter(
                DuplicateAnalysisResult.task_id == regional_data.task_id
                # 注意：DuplicateAnalysisResult没有petition_record_id字段，所以只通过task_id关联
            ).first()

            # 查询相关记录
            related_records = self.get_related_records(regional_data.petition_record_id)

            return {
            'basic_info': {
                'petition_record_id': regional_data.petition_record_id,
                'analysis_task_id': regional_data.task_id,
                'petitioner_name': regional_data.tsr,
                'respondent_unit': regional_data.ts_qxdw,
                'region': regional_data.ts_xzq,
                'province': regional_data.dzxx_ds,  # 地址信息_行政区
                'city': regional_data.dzxx_xzq,    # 地址信息_行政区
                'district': regional_data.dzxx_jd,   # 地址信息_街道
                'analysis_status': 'completed',  # 默认状态
                'created_at': regional_data.created_at.isoformat(),
                'updated_at': regional_data.updated_at.isoformat()
            },
            'regional_analysis': {
                'id': regional_data.id,
                'region': regional_data.ts_xzq,
                'province': regional_data.dzxx_ds,  # 地址信息_行政区
                'city': regional_data.dzxx_xzq,    # 地址信息_行政区
                'district': regional_data.dzxx_jd,   # 地址信息_街道
                'longitude': regional_data.dzxx_lng,
                'latitude': regional_data.dzxx_lat,
                'confidence': regional_data.cfxs_gl
            } if regional_data else None,
            'complaint_type_analysis': {
                'id': complaint_data.id,
                'primary_type': complaint_data.tslx_yj,
                'secondary_type': complaint_data.tslx_rj,
                'tertiary_type': complaint_data.tslx_sj,
                'confidence': max(complaint_data.get_level2_confidence(), complaint_data.get_level3_confidence()),
                'primary_confidence': 1.0,  # 默认值
                'secondary_confidence': complaint_data.get_level2_confidence(),
                'tertiary_confidence': complaint_data.get_level3_confidence()
            } if complaint_data else None,
            'sentiment_analysis': {
                'id': sentiment_data.id,
                'sentiment_type': sentiment_data.qgfx_lx,
                'sentiment_score': sentiment_data.get_sentiment_intensity(),
                'summary': sentiment_data.qgfx_zy,
                'keywords': sentiment_data.get_keywords_list(),
                'confidence': sentiment_data.get_confidence_score()
            } if sentiment_data else None,
            'compliance_analysis': {
                'id': compliance_data.id,
                'is_compliant': compliance_data.is_compliant(),
                'compliance_score': 100 if compliance_data.is_compliant() else 0,
                'reason': compliance_data.gffx_pdly,
                'suggestions': '无建议'  # 默认值
            } if compliance_data else None,
            'duplicate_analysis': {
                'id': duplicate_data.id,
                'is_duplicate': bool(duplicate_data.cfbh),
                'similarity_score': 1.0,  # 默认值
                'duplicated_with': duplicate_data.cfbh,
                'duplicate_reason': '重复信访'
            } if duplicate_data else None,
            'related_records': related_records
        }

        except Exception as e:
            logger.error(f"获取信访台账详情失败: {str(e)}")
            raise Exception(f"获取信访台账详情失败: {str(e)}")


    def export_petition_ledger(self, export_params: Dict[str, Any]) -> str:
        """
        导出信访台账数据
        """
        try:
            # 获取数据
            result = self.get_petition_ledger_list(
                page=1,
                page_size=10000,  # 大量数据导出
                region=export_params.get('region'),
                petitioner_name=export_params.get('petitioner_name'),
                respondent_unit=export_params.get('respondent_unit'),
                analysis_status=export_params.get('analysis_status'),
                date_from=export_params.get('date_from'),
                date_to=export_params.get('date_to'),
                sentiment_type=export_params.get('sentiment_type'),
                complaint_type=export_params.get('complaint_type'),
                task_id=export_params.get('task_id')
            )

            # 转换为DataFrame
            df = pd.DataFrame(result['items'])

            # 创建导出目录
            export_dir = 'exports'
            os.makedirs(export_dir, exist_ok=True)

            # 生成文件名
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            export_format = export_params.get('export_format', 'excel')

            if export_format == 'excel':
                filename = f'petition_ledger_{timestamp}.xlsx'
                filepath = os.path.join(export_dir, filename)
                df.to_excel(filepath, index=False, engine='openpyxl')
            elif export_format == 'csv':
                filename = f'petition_ledger_{timestamp}.csv'
                filepath = os.path.join(export_dir, filename)
                df.to_csv(filepath, index=False, encoding='utf-8-sig')
            elif export_format == 'json':
                filename = f'petition_ledger_{timestamp}.json'
                filepath = os.path.join(export_dir, filename)
                df.to_json(filepath, orient='records', force_ascii=False, indent=2)
            else:
                raise ValueError(f"不支持的导出格式: {export_format}")

            return filepath

        except Exception as e:
            raise Exception(f"导出失败: {str(e)}")

    def get_petition_ledger_summary(self) -> Dict[str, Any]:
        """
        获取信访台账统计摘要
        """
        try:
            # 总数统计
            total_records = self.db.query(RegionalAnalysisResult).count()

            # 按区域统计 - 使用 ts_xzq (问题属地) 字段
            region_stats = self.db.query(
                RegionalAnalysisResult.ts_xzq,
                func.count(RegionalAnalysisResult.id)
            ).filter(RegionalAnalysisResult.ts_xzq.isnot(None)).group_by(RegionalAnalysisResult.ts_xzq).limit(10).all()

            # 按省份统计 - 使用 dzxx_ds (地址信息_行政区) 字段
            province_stats = self.db.query(
                RegionalAnalysisResult.dzxx_ds,
                func.count(RegionalAnalysisResult.id)
            ).filter(RegionalAnalysisResult.dzxx_ds.isnot(None)).group_by(RegionalAnalysisResult.dzxx_ds).limit(10).all()

            # 情感分析统计
            sentiment_stats = self.db.query(
                SentimentAnalysisResult.qgfx_lx,
                func.count(SentimentAnalysisResult.id)
            ).filter(SentimentAnalysisResult.qgfx_lx.isnot(None)).group_by(SentimentAnalysisResult.qgfx_lx).all()

            # 投诉类型统计
            complaint_stats = self.db.query(
                ComplaintTypeAnalysisResult.tslx_yj,
                func.count(ComplaintTypeAnalysisResult.id)
            ).filter(ComplaintTypeAnalysisResult.tslx_yj.isnot(None)).group_by(ComplaintTypeAnalysisResult.tslx_yj).limit(10).all()

            # 规范性分析统计
            compliance_stats = self.db.query(
                ComplianceAnalysisResult.gffx_zt,
                func.count(ComplianceAnalysisResult.id)
            ).filter(ComplianceAnalysisResult.gffx_zt.isnot(None)).group_by(ComplianceAnalysisResult.gffx_zt).all()

            return {
                'total_records': total_records,
                'region_statistics': [
                    {'region': region, 'count': count} for region, count in region_stats
                ],
                'province_statistics': [
                    {'province': province, 'count': count} for province, count in province_stats
                ],
                'sentiment_statistics': [
                    {'sentiment_type': sentiment_type, 'count': count} for sentiment_type, count in sentiment_stats
                ],
                'complaint_statistics': [
                    {'complaint_type': complaint_type, 'count': count} for complaint_type, count in complaint_stats
                ],
                'compliance_statistics': [
                    {'compliance_status': '合规' if status == '1' else '不合规', 'count': count} for status, count in compliance_stats
                ]
            }

        except Exception as e:
            logger.error(f"获取统计摘要失败: {str(e)}")
            raise Exception(f"获取统计摘要失败: {str(e)}")

    def get_related_records(self, petition_record_id: int) -> List[Dict[str, Any]]:
        """
        获取相关记录 - 查询同一信访记录的其他分析结果
        优化查询性能，限制返回结果数量
        """
        try:
            related_records = self.db.query(RegionalAnalysisResult).filter(
                RegionalAnalysisResult.petition_record_id == petition_record_id
            ).order_by(desc(RegionalAnalysisResult.created_at)).limit(10).all()  # 限制返回10条记录

            result = []
            for record in related_records:
                result.append({
                    'id': record.id,
                    'analysis_task_id': record.task_id,
                    'region': record.ts_xzq,
                    'analysis_status': 'completed',  # 默认状态
                    'created_at': record.created_at.isoformat()
                })

            return result
        except Exception as e:
            logger.error(f"获取相关记录失败: {str(e)}")
            return []  # 返回空列表而不是抛出异常